from flask import Flask, request, redirect, session, render_template
import sqlite3
from functools import wraps
import time

app = Flask(__name__)
app.secret_key = 'your_secret_key'  # 用于加密session，确保安全性


# 初始化SQLite数据库
def init_db():
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        username TEXT UNIQUE,
                        password TEXT)''')
    conn.commit()
    conn.close()


# 初始化帖子数据库
def init_passage_db():
    conn = sqlite3.connect('passage.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS posts (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        user TEXT,
                        content TEXT,
                        post_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        timestamp_col INTEGER UNIQUE)''')
    cursor.execute('''CREATE TABLE IF NOT EXISTS replies (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        post_id INTEGER,
                        user TEXT,
                        content TEXT,
                        reply_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (post_id) REFERENCES posts (id))''')
    conn.commit()
    conn.close()

# 检查用户是否存在以及密码是否正确
def check_user(username):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT password FROM users WHERE username = ?', (username,))
    user = cursor.fetchone()  # 获取查询结果
    conn.close()
    return user  # 返回用户记录（包括密码）


# 注册新用户
def register_user(username, password):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    try:
        cursor.execute('INSERT INTO users (username, password) VALUES (?, ?)', (username, password))
        conn.commit()
    except sqlite3.IntegrityError:
        pass  # 如果用户名已存在，不做任何操作
    conn.close()


# 登录验证装饰器
def login_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        if 'username' not in session:
            return redirect(f"/login?next={request.url}")  # 未登录，跳转到登录页面
        return f(*args, **kwargs)

    return decorated_function


@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['Username']
        password = request.form['Password']

        user = check_user(username)  # 获取用户信息

        if user:
            # 用户存在，验证密码
            if user[0] == password:
                session['username'] = username  # 登录成功，设置session
                return redirect(request.args.get('next') or '/welcome')  # 密码正确，跳转到欢迎页面
            else:
                return redirect('/login_off')  # 密码错误，跳转到错误页面
        else:
            # 用户不存在，注册该用户
            register_user(username, password)
            return redirect('/reg_on')  # 用户注册，跳转

    return render_template('login.html')


@app.route("/login_off")
def login_off():
    return render_template('login_off.html')


@app.route("/reg_on")
def reg_on():
    return render_template('reg_on.html')


@app.route("/welcome")
@login_required  # 需要登录才能访问
def welcome():
    username = session['username']
    return render_template('welcome.html', username=username)


@app.route('/admin')
@login_required
def list_page():
    if session['username'] != 'admin':
        return render_template('admin_off.html', )
    # 查询数据库中的所有用户信息
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT username, password FROM users')
    users = cursor.fetchall()  # 获取所有用户信息
    conn.close()
    return render_template('admin.html', users=users)


@app.route('/messageboard', methods=['GET', 'POST'])
@login_required
def messageboard():
    if request.method == 'POST':
        content = request.form['content']
        if content:
            current_time = int(time.time())  # 获取当前时间戳
            conn = sqlite3.connect('passage.db')
            cursor = conn.cursor()
            cursor.execute('INSERT INTO posts (user, content, timestamp_col) VALUES (?, ?, ?)',
                           (session['username'], content, current_time))
            conn.commit()
            conn.close()
            return redirect('/messageboard')  # 提交后重定向，避免重复提交

    # 处理搜索请求
    search_query = request.args.get('search', '')
    conn = sqlite3.connect('passage.db')
    cursor = conn.cursor()
    if search_query:
        cursor.execute('''
            SELECT id, user, content, post_time, last_modified, timestamp_col 
            FROM posts 
            WHERE content LIKE ?
            ORDER BY post_time DESC
        ''', (f'%{search_query}%',))
    else:
        cursor.execute('''
            SELECT id, user, content, post_time, last_modified, timestamp_col 
            FROM posts 
            ORDER BY post_time DESC
        ''')
    posts = cursor.fetchall()
    conn.close()
    return render_template('messageboard.html', posts=posts, search_query=search_query)


@app.route('/p/<timestamp>')
def post_detail(timestamp):
    conn = sqlite3.connect('passage.db')
    cursor = conn.cursor()
    cursor.execute('SELECT id, user, content, post_time, last_modified FROM posts WHERE timestamp_col = ?', (timestamp,))  # 使用正确的列名 timestamp_col
    post = cursor.fetchone()
    if post:
        post_id = post[0]
        cursor.execute('SELECT user, content, reply_time FROM replies WHERE post_id = ?', (post_id,))
        replies = cursor.fetchall()
        conn.close()
        return render_template('post_detail.html', post=post, replies=replies)
    else:
        return redirect('/messageboard')


@app.route('/reply/<post_id>', methods=['POST'])
@login_required
def reply(post_id):
    content = request.form['reply_content']
    if content:
        conn = sqlite3.connect('passage.db')
        cursor = conn.cursor()
        cursor.execute('INSERT INTO replies (post_id, user, content) VALUES (?, ?, ?)',
                       (post_id, session['username'], content))
        cursor.execute('UPDATE posts SET last_modified = CURRENT_TIMESTAMP WHERE id = ?', (post_id,))
        conn.commit()
        conn.close()
    return redirect(f'/p/{post_id}')


@app.route('/')
def mainpage():
    if 'username' in session:
        return redirect('/welcome')  # 已登录
    return redirect('/login')  # 未登录


if __name__ == "__main__":
    init_db()  # 确保用户数据库和表格已创建
    init_passage_db()  # 确保帖子数据库和表格已创建
    app.run(debug=True)